﻿using System;
using System.ComponentModel;
using System.Data.SqlClient;
using QLHS.DTO;

namespace QLHS.DataLayer
{
    public class LopDAO
    {
        public BindingList<LopDTO> LoadAll()
        {
            SqlConnection cn = new SqlConnection(Helper.ConnectionString);
            string sql = "select * from LOPHOC";
            BindingList<LopDTO> LopTable = new BindingList<LopDTO>();

            try
            {
                SqlCommand cmd = new SqlCommand(sql, cn);
                cn.Open();
                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        int _MSLop = Convert.ToInt32(reader["MaSoLop"].ToString());
                        string _TenLop = reader["TenLop"].ToString();
                        int _Khoi = Convert.ToInt32(reader["Khoi"].ToString());
                        int _Siso = Convert.ToInt32(reader["Siso"].ToString());
                        int _GVCN = Convert.ToInt32(reader["GVCN"].ToString());

                        LopDTO temp = new LopDTO
                        {
                            MaSoLop = _MSLop,
                            TenLop = _TenLop,
                            Khoi = _Khoi,
                            Siso = _Siso,
                            GVCN = _GVCN,
                        };
                        LopTable.Add(temp);
                    }
                }
            }
            finally
            {
                if (cn != null && cn.State == System.Data.ConnectionState.Open)
                {
                    cn.Close();
                }
            }

            return LopTable;
        }

        public void Delete(LopDTO del)
        {
            string sql = string.Format("delete from LOPHOC where MaSoLop = {0}", del.MaSoLop);
            SqlConnection cn = new SqlConnection(Helper.ConnectionString);

            try
            {
                SqlCommand cmd = new SqlCommand(sql, cn);
                cn.Open();
                cmd.ExecuteNonQuery();
            }
            finally
            {
                if (cn != null && cn.State == System.Data.ConnectionState.Open)
                {
                    cn.Close();
                }
            }            
        }

        public void Insert(LopDTO InsertObject)
        {
            string sql;
            if (InsertObject.GVCN.HasValue)
            {
                sql = string.Format("insert into LOPHOC values (N'{0}', {1}, {2}, {3})",
                InsertObject.TenLop,
                InsertObject.Khoi,
                InsertObject.Siso,
                InsertObject.GVCN);
            }
            else
            {
                sql = string.Format("insert into LOPHOC values (N'{0}', {1}, {2}, NULL)",
               InsertObject.TenLop,
               InsertObject.Khoi,
               InsertObject.Siso);
            }
            SqlConnection cn = new SqlConnection(Helper.ConnectionString);

            try
            {
                SqlCommand cmd = new SqlCommand(sql, cn);
                cn.Open();
                cmd.ExecuteNonQuery();
            }
            finally
            {
                if (cn != null && cn.State == System.Data.ConnectionState.Open)
                {
                    cn.Close();
                }
            }
        }

        public void Update(LopDTO UpdateObject)
        {
            string sql;
            if (UpdateObject.GVCN.HasValue)
            {
                sql= string.Format("update LOPHOC set TenLop = N'{1}' , Khoi = {2} , Siso = {3} , GVCN = {4}  where MaSoLop = {0}",
                UpdateObject.MaSoLop, 
                UpdateObject.TenLop, 
                UpdateObject.Khoi, 
                UpdateObject.Siso, 
                UpdateObject.GVCN);
            }
            else{
                sql = string.Format("update LOPHOC set TenLop = N'{1}' , Khoi = {2} , Siso = {3} where MaSoLop = {0}",
              UpdateObject.MaSoLop, 
              UpdateObject.TenLop,
              UpdateObject.Khoi, 
              UpdateObject.Siso);
            }

            SqlConnection cn = new SqlConnection(Helper.ConnectionString);
            try
            {
                SqlCommand cmd = new SqlCommand(sql, cn);
                cn.Open();
                cmd.ExecuteNonQuery();
            }
            finally
            {
                if (cn != null && cn.State == System.Data.ConnectionState.Open)
                {
                    cn.Close();
                }
            }
        }

        //public void ChangeKeyValue(string Old_MSLop, string New_MSLop)
        //{
        //    string sql = string.Format("update Lop set MSLop = '{0}' where (MSLop = '{1}')", New_MSLop, Old_MSLop);

        //    SqlConnection Cn = new SqlConnection(Helper.ConnectionString);
        //    SqlCommand Cmd = new SqlCommand(sql, Cn);
        //    Cn.Open();
        //    Cmd.ExecuteNonQuery();
        //    Cn.Close();
        //}

        public BindingList<LopDTO> GetLopataByPrimaryKey(int MSLop)
        {
            SqlConnection cn = new SqlConnection(Helper.ConnectionString);
            string sql = string.Format("select* from LOPHOC where MaSoLop = {0}", MSLop);
            BindingList<LopDTO> Result = new BindingList<LopDTO>();

            try
            {
                SqlCommand cmd = new SqlCommand(sql, cn);
                cn.Open();
                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        int _MSLop = Convert.ToInt32(reader["MaSoLop"].ToString());
                        string _TenLop = reader["TenLop"].ToString();
                        int _Khoi = Convert.ToInt32(reader["Khoi"].ToString());
                        int _Siso = Convert.ToInt32(reader["Siso"].ToString());
                        int _GVCN = Convert.ToInt32(reader["GVCN"].ToString());

                        LopDTO temp = new LopDTO
                        {
                            MaSoLop = _MSLop,
                            TenLop = _TenLop,
                            Khoi = _Khoi,
                            Siso = _Siso,
                            GVCN = _GVCN,
                        };
                        Result.Add(temp);
                    }
                }
            }
            finally
            {
                if (cn != null && cn.State == System.Data.ConnectionState.Open)
                {
                    cn.Close();
                }
            }

            return Result;
        }
    }
}
